﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Collections;
using System.Web;

namespace DAL
{
    public class QuanLyChiTietTKQuery:DataProvide
    {
        public static bool Them(string matk, string madon, float tongthu, float tongchi, float loinhuan)
        {
            SqlConnection ketNoi = null;
            bool ketQua = true;
            try
            {
                ketNoi = KetNoi();
                string insertCommand = "insert into CTThongKeBaoCao (MaTKBC, MaDonDatHang, TongThu, TongChi, LoiNhuan) values(N'" + matk + "','" + madon + "', '"+ tongthu +"', '"+ tongchi +"', '"+ loinhuan +"')";


                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                lenh.ExecuteNonQuery();
            }
            catch (Exception)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }

        public static bool Xoa(string matk, string madon)
        {
            SqlConnection ketNoi = null;
            bool ketQua = true;
            try
            {
                ketNoi = KetNoi();
                string insertCommand = "delete from   CTThongKeBaoCao  where	MaTKBC ='" + matk + "' and MaDonDatHang = '"+ madon +"' ";

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                lenh.ExecuteNonQuery();
            }
            catch (Exception)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }

        public static bool Sua(string matk, string madon, float tongthu, float tongchi, float loinhuan)
        {
            SqlConnection ketNoi = null;
            bool ketQua = true;
            try
            {
                ketNoi = KetNoi();
                string insertCommand = "UPDATE	CTThongKeBaoCao SET TongThu = '" + tongthu + "', TongChi = '"+ tongchi +"', LoiNhuan = '"+ loinhuan +"' WHERE MaTKBC = '"+ matk +"' and MaDonDatHang = '"+ madon +"' ";

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                lenh.ExecuteNonQuery();

            }
            catch (Exception)
            {
                ketQua = false;

            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }

        public static List<ThongKeDAL> Tim(string thongke, string dondathang)
        {
            SqlConnection ketNoi = null;
            List<ThongKeDAL> dsThongKe = new List<ThongKeDAL>();
            try
            {
                ketNoi = KetNoi();

                string insertCommand = "Select CTThongKeBaoCao.MaTKBC, CTThongKeBaoCao.MaDonDatHang, CTThongKeBaoCao.TongThu, CTThongKeBaoCao.TongChi, CTThongKeBaoCao.LoiNhuan, ThongKeBaoCao.NgayTKBC from CTThongKeBaoCao, ThongKeBaoCao where ThongKeBaoCao.MaTKBC = CTThongKeBaoCao.MaTKBC and CTThongKeBaoCao.MaTKBC like '%" + thongke + "%' and CTThongKeBaoCao.MaDonDatHang like '%" + dondathang + "%'";

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                SqlDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    ThongKeDAL tk = new ThongKeDAL();
                    tk.MaThongKe = boDoc.GetString(0);
                    if (!boDoc.IsDBNull(1))
                        tk.MaDonDatHang = boDoc.GetInt32(1);
                    if (!boDoc.IsDBNull(2))
                        tk.TongThu = float.Parse(boDoc.GetDouble(2).ToString());
                    if (!boDoc.IsDBNull(3))
                        tk.TongChi = float.Parse(boDoc.GetDouble(3).ToString());
                    if (!boDoc.IsDBNull(4))
                        tk.LoiNhuan = float.Parse(boDoc.GetDouble(4).ToString());
                    if (!boDoc.IsDBNull(5))
                        tk.NgayThongKe = boDoc.GetDateTime(5);
                    dsThongKe.Add(tk);
                }
            }
            catch (Exception)
            {
                dsThongKe = new List<ThongKeDAL>();
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return dsThongKe;
        }
    }
}
